﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;

using Global.Class.Library;
using DataTransferObject;
using DataAccessLayer;
using DataAccessLayer.MarketingPaymentDTSTableAdapters;


namespace BusinessLogicLayer
{
    public class MarketingMonitoringBLL : NotifyPropertyChangeObject
    {
        private enum CustomFilterTypeID
        {
            ByBeginingYear = 1,
            ByBeginingMonth = 2,
            ByMarketingProgramID = 3
        }

        public GlobalEnum.TaskID TaskID { get { return GlobalEnum.TaskID.MarketingMonitoring; } }

        private UserInformation userOrganization;

        private bool ReadonlyMode { get; set; }

        public MarketingMonitoringBLL()
        {
            try
            {
                if (GlobalVariables.shouldRestoreProcedure) RestoreProcedure();

                userOrganization = new UserInformation();



                this.arrayFilterDescription = new string[4, 2];

                this.dataSetFilter = new DataSet();


                #region Filter by MarketingProgramID

                this.dataTableFilterMarketingProgram = SQLDatabase.GetDataTable(" SELECT TOP 1          " + (int)CustomFilterTypeID.ByBeginingYear + " AS PrimaryFilterOption, 0 AS PrimaryFilterID, '[All Programs]' AS [MarketingProgram], -1 AS AncestorID FROM PublicMasterProfile " +
                                                                                " UNION ALL    " +
                                                                                " SELECT       DISTINCT " + (int)CustomFilterTypeID.ByBeginingYear + " AS PrimaryFilterOption, 10000000 - YEAR(BeginingDate) AS PrimaryFilterID, CAST(YEAR(BeginingDate) AS nvarchar(4)) AS [MarketingProgram], 0 AS AncestorID FROM MarketingProgramMaster " +
                                                                                " UNION ALL    " +
                                                                                " SELECT       DISTINCT " + (int)CustomFilterTypeID.ByBeginingMonth + " AS PrimaryFilterOption, 100000000 - CAST(LEFT(CONVERT(nvarchar(30), BeginingDate, 112), 6) AS int) AS PrimaryFilterID, RIGHT(CONVERT(nvarchar(30), BeginingDate, 103), 7) AS [MarketingProgram], 10000000 - YEAR(BeginingDate) AS AncestorID FROM MarketingProgramMaster " +
                                                                                " UNION ALL    " +
                                                                                " SELECT                " + (int)CustomFilterTypeID.ByMarketingProgramID + " AS PrimaryFilterOption, MarketingProgramID AS PrimaryFilterID, Reference AS [MarketingProgram], 100000000 - CAST(LEFT(CONVERT(nvarchar(30), BeginingDate, 112), 6) AS int) AS AncestorID FROM MarketingProgramMaster ");
                this.dataTableFilterMarketingProgram.TableName = "TableFilterMarketingProgram";

                DataColumn dataColumnCheckBox = new DataColumn("V", typeof(bool));
                dataColumnCheckBox.DefaultValue = false;
                this.dataTableFilterMarketingProgram.Columns.Add(dataColumnCheckBox);

                this.dataSetFilter.Tables.Add(this.dataTableFilterMarketingProgram);

                #endregion Filter by MarketingProgramID


                #region Filter by ItemCategory + ItemCommodity

                this.dataTableFilterCommodity = SQLDatabase.GetDataTable(" SELECT TOP 1 " + (int)GlobalEnum.TaskID.ItemCategory + " AS PrimaryFilterOption, 0 AS PrimaryFilterID, '[All Items]' AS [Commodities], -1 AS AncestorID FROM PublicMasterProfile " +
                                                                         " UNION ALL    " +
                                                                         " SELECT       " + (int)GlobalEnum.TaskID.ItemCategory + " AS PrimaryFilterOption, ListItemCategory.ItemCategoryID AS PrimaryFilterID, ListItemCategory.Description AS [Commodities], CASE ListItemCategory.AncestorID WHEN 61 THEN 0 ELSE ListItemCategory.AncestorID END AS AncestorID FROM ListItemCategory WHERE HierarchyLevel >= 2 "); //Hard coding (AncestorID = 61, HierarchyLevel > =2) To adapt to Mr Cuong Requirement
                //" UNION ALL    " + //Mr Cuong at UIP request that it should filter by BRAND and ITS GROUP only (No need to filter by father category and ist commodity)
                //" SELECT       " + (int)GlobalEnum.TaskID.ItemCommodity + " AS PrimaryFilterOption, 50000 + ListItemCommodity.CommodityID AS PrimaryFilterID, ListItemCommodity.DescriptionOfficial AS [Commodities], ListItemCommodity.ItemCategoryID AS AncestorID FROM ListItemCommodity ");


                this.dataTableFilterCommodity.TableName = "TableFilterCommodity";

                dataColumnCheckBox = new DataColumn("V", typeof(bool));
                dataColumnCheckBox.DefaultValue = false;
                this.dataTableFilterCommodity.Columns.Add(dataColumnCheckBox);

                this.dataSetFilter.Tables.Add(this.dataTableFilterCommodity);

                #endregion Filter by ItemCategory + ItemCommodity



                #region Filter by CustomerChannel
                this.dataTableFilterCustomer = SQLDatabase.GetDataTable(" SELECT TOP 1 " + (int)GlobalEnum.TaskID.CustomerChannel + " AS PrimaryFilterOption, 0 AS PrimaryFilterID, '[All Customers]' AS [Customers], -1 AS AncestorID FROM PublicMasterProfile " +
                                                                        " UNION ALL    " +
                                                                        " SELECT       " + (int)GlobalEnum.TaskID.CustomerChannel + " AS PrimaryFilterOption, ListCustomerChannel.CustomerChannelID AS PrimaryFilterID, ListCustomerChannel.Description AS [Customers], ListCustomerChannel.AncestorID FROM ListCustomerChannel ");

                this.dataTableFilterCustomer.TableName = "TableFilterCustomer";

                dataColumnCheckBox = new DataColumn("V", typeof(bool));
                dataColumnCheckBox.DefaultValue = false;
                this.dataTableFilterCustomer.Columns.Add(dataColumnCheckBox);


                this.dataSetFilter.Tables.Add(this.dataTableFilterCustomer);

                #endregion Filter by CustomerChannel



                GlobalDefaultValue.Apply(this);

            }
            catch (Exception exception)
            {
                throw exception;
            }
        }





        #region <Storage>

        public UserInformation UserOrganization
        {
            get { return this.userOrganization; }
            set { this.userOrganization = value; }
        }

        private string[,] arrayFilterDescription;

        /// <summary>
        /// This get user friendly filter description from the arrayFilterDescription, BUT the arrayFilterDescription is only updated by this.GetFilterMarketingProgramID()
        /// So: To use this FilterDescription, please ONLY TAKE IT AFTER SOME METHED HAS CALLED This.GetFilterMarketingProgramID()
        /// </summary>
        /// <returns></returns>
        public string[,] FilterDescription()
        {
            return arrayFilterDescription;
        }

        private DataTable GetFilterMarketingProgramID()
        {
            string listItemCategoryID = ""; string listCommodityID = ""; string listCustomerChannelID = ""; string listBeginingYear = ""; string listBeginingMonth = ""; string listMarketingProgramID = ""; DataTable tableMarketingProgramID;
            string listItemCategoryDescription = ""; string listCommodityDescription = ""; string listCustomerChannelDescription = ""; string listBeginingYearDescription = ""; string listBeginingMonthDescription = ""; string listMarketingProgramDescription = "";  //These varible are used to get the user friendly filter description of the filter

            #region Filter by Commodity

            DataRow[] dataRowFilterArray = this.dataTableFilterCommodity.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)GlobalEnum.TaskID.ItemCategory + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listItemCategoryID = ""; listItemCategoryDescription = "[ALL]"; break; }
                else
                {
                    listItemCategoryID = listItemCategoryID + (listItemCategoryID == "" ? "" : ";") + dataRowFilterArray[i]["PrimaryFilterID"].ToString();
                    listItemCategoryDescription = listItemCategoryDescription + (listItemCategoryDescription == "" ? "" : "; ") + dataRowFilterArray[i]["Commodities"].ToString();
                }
            }

            dataRowFilterArray = this.dataTableFilterCommodity.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)GlobalEnum.TaskID.ItemCommodity + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listCommodityID = ""; listItemCategoryDescription = "[ALL]"; listCommodityDescription = ""; break; }
                else
                {
                    listCommodityID = listCommodityID + (listCommodityID == "" ? "" : ";") + ((int)dataRowFilterArray[i]["PrimaryFilterID"] - 50000).ToString();
                    listCommodityDescription = listCommodityDescription + (listCommodityDescription == "" ? "" : "; ") + dataRowFilterArray[i]["Commodities"].ToString();
                }
            }

            #endregion Filter by Commodity

            #region Filter by Customer
            dataRowFilterArray = this.dataTableFilterCustomer.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)GlobalEnum.TaskID.CustomerChannel + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listCustomerChannelID = ""; listCustomerChannelDescription = "[ALL]"; break; }
                else
                {
                    listCustomerChannelID = listCustomerChannelID + (listCustomerChannelID == "" ? "" : ";") + dataRowFilterArray[i]["PrimaryFilterID"].ToString();
                    listCustomerChannelDescription = listCustomerChannelDescription + (listCustomerChannelDescription == "" ? "" : "; ") + dataRowFilterArray[i]["Customers"].ToString();
                }
            }
            #endregion Filter by Customer

            #region Filter by Year/ Month/ MarketingProgramID
            dataRowFilterArray = this.dataTableFilterMarketingProgram.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)CustomFilterTypeID.ByBeginingYear + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listBeginingYear = ""; listBeginingYearDescription = "[ALL]"; break; }
                else
                {
                    listBeginingYear = listBeginingYear + (listBeginingYear == "" ? "" : ";") + (10000000 - (int)dataRowFilterArray[i]["PrimaryFilterID"]).ToString();
                    listBeginingYearDescription = listBeginingYearDescription + (listBeginingYearDescription == "" ? "" : "; ") + dataRowFilterArray[i]["MarketingProgram"].ToString();
                }
            }

            dataRowFilterArray = this.dataTableFilterMarketingProgram.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)CustomFilterTypeID.ByBeginingMonth + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listBeginingMonth = ""; listBeginingYearDescription = "[ALL]"; listBeginingMonthDescription = ""; break; }
                else
                {
                    listBeginingMonth = listBeginingMonth + (listBeginingMonth == "" ? "" : ";") + (100000000 - (int)dataRowFilterArray[i]["PrimaryFilterID"]).ToString();
                    listBeginingMonthDescription = listBeginingMonthDescription + (listBeginingMonthDescription == "" ? "" : "; ") + dataRowFilterArray[i]["MarketingProgram"].ToString();
                }
            }

            dataRowFilterArray = this.dataTableFilterMarketingProgram.Select("(PrimaryFilterID = 0 OR PrimaryFilterOption = " + (int)CustomFilterTypeID.ByMarketingProgramID + ") AND V = 1", "PrimaryFilterID");
            for (int i = dataRowFilterArray.GetLowerBound(0); i <= dataRowFilterArray.GetUpperBound(0); i++)
            {
                if ((int)dataRowFilterArray[i]["PrimaryFilterID"] == 0)
                { listMarketingProgramID = ""; listBeginingYearDescription = "[ALL]"; listBeginingMonthDescription = ""; listMarketingProgramDescription = ""; break; }
                else
                {
                    listMarketingProgramID = listMarketingProgramID + (listMarketingProgramID == "" ? "" : ";") + dataRowFilterArray[i]["PrimaryFilterID"].ToString();
                    listMarketingProgramDescription = listMarketingProgramDescription + (listMarketingProgramDescription == "" ? "" : "; ") + dataRowFilterArray[i]["MarketingProgram"].ToString();
                }
            }

            SqlParameter[] sqlParameter = new SqlParameter[3];
            sqlParameter[0] = new SqlParameter("@listBeginingYear", listBeginingYear); sqlParameter[0].SqlDbType = SqlDbType.VarChar; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@listBeginingMonth", listBeginingMonth); sqlParameter[1].SqlDbType = SqlDbType.VarChar; sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[2] = new SqlParameter("@listMarketingProgramID", listMarketingProgramID); sqlParameter[2].SqlDbType = SqlDbType.VarChar; sqlParameter[2].Direction = ParameterDirection.Input;

            tableMarketingProgramID = SQLDatabase.GetDataTable("MarketingYearMonthIDFilter", CommandType.StoredProcedure, sqlParameter);
            #endregion Filter by Year/ Month/ MarketingProgramID


            arrayFilterDescription[0, 0] = "Date"; arrayFilterDescription[0, 1] = GlobalVariables.GlobalOptionSetting.LowerFillterDate.ToString("dd/MM/yyyy") + " -> " + GlobalVariables.GlobalOptionSetting.UpperFillterDate.ToString("dd/MM/yyyy"); 
            arrayFilterDescription[1, 0] = "Item"; arrayFilterDescription[1, 1] = listItemCategoryDescription + (listItemCategoryDescription != "" && listCommodityDescription != "" ? "; " : "") + listCommodityDescription;
            arrayFilterDescription[2, 0] = "Channel"; arrayFilterDescription[2, 1] = listCustomerChannelDescription;
            arrayFilterDescription[3, 0] = "Program Code"; arrayFilterDescription[3, 1] = listBeginingYearDescription + (listBeginingYearDescription != "" && listBeginingMonthDescription != "" ? "; " : "") + listBeginingMonthDescription + ((listBeginingYearDescription != "" || listBeginingMonthDescription != "") && listMarketingProgramDescription != "" ? "; " : "") + listMarketingProgramDescription;
            
            
            //Main
            sqlParameter = new SqlParameter[6];
            sqlParameter[0] = new SqlParameter("@LowerFillterDate", GlobalVariables.GlobalOptionSetting.LowerFillterDate); sqlParameter[0].SqlDbType = SqlDbType.DateTime; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@UpperFillterDate", GlobalVariables.GlobalOptionSetting.UpperFillterDate); sqlParameter[1].SqlDbType = SqlDbType.DateTime; sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[2] = new SqlParameter("@ListItemCategoryID", listItemCategoryID); sqlParameter[2].SqlDbType = SqlDbType.VarChar; sqlParameter[2].Direction = ParameterDirection.Input;
            sqlParameter[3] = new SqlParameter("@ListCommodityID", listCommodityID); sqlParameter[3].SqlDbType = SqlDbType.VarChar; sqlParameter[3].Direction = ParameterDirection.Input;
            sqlParameter[4] = new SqlParameter("@ListCustomerChannelID", listCustomerChannelID); sqlParameter[4].SqlDbType = SqlDbType.VarChar; sqlParameter[4].Direction = ParameterDirection.Input;
            sqlParameter[5] = new SqlParameter("@MarketingProgramIDList", tableMarketingProgramID); sqlParameter[5].SqlDbType = SqlDbType.Structured; sqlParameter[5].TypeName = "dbo.IdentityTable"; sqlParameter[5].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingMonitoringFilter", CommandType.StoredProcedure, sqlParameter);

        }

        public DataTable GetMasterList()
        {
            DataTable tableMarketingProgramID = this.GetFilterMarketingProgramID();

            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MarketingProgramIDList", tableMarketingProgramID); sqlParameter[0].SqlDbType = SqlDbType.Structured; sqlParameter[0].TypeName = "dbo.IdentityTable"; sqlParameter[0].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable(this.MonitoringOption, CommandType.StoredProcedure, sqlParameter);
        }


        public DataTable GetDetailList(int filterID)
        {
            DataTable table;
            if (this.MonitoringOption == "MarketingMonitoringByProgram")
            {
                table = new DataTable();
                table.Columns.Add("MarketingProgramID", typeof(int));
                table.Rows.Add(filterID);
            }
            else
            {
                table = this.GetFilterMarketingProgramID();
            }

            SqlParameter[] sqlParameter = new SqlParameter[3];
            sqlParameter[0] = new SqlParameter("@MarketingProgramIDList", table); sqlParameter[0].SqlDbType = SqlDbType.Structured; sqlParameter[0].TypeName = "dbo.IdentityTable"; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@CustomerID", (this.MonitoringOption == "MarketingMonitoringByProgram" ? 0 : filterID)); sqlParameter[1].SqlDbType = SqlDbType.Int; sqlParameter[1].Direction = ParameterDirection.Input;
            sqlParameter[2] = new SqlParameter("@IncludeApplyItem", (this.MonitoringOption == "MarketingMonitoringByProgram" ? 0 : 1)); sqlParameter[2].Value = (this.MonitoringOption == "MarketingMonitoringByProgram" ? 0 : 1); sqlParameter[2].SqlDbType = SqlDbType.Bit; sqlParameter[2].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingMonitoringByPayment", CommandType.StoredProcedure, sqlParameter);
        }


        public DataTable GetMarketingIncentiveJournal(int marketingProgramID, int customerID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@MarketingProgramID", marketingProgramID); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@CustomerID", customerID); sqlParameter[1].SqlDbType = SqlDbType.Int; sqlParameter[1].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingIncentiveJournal", CommandType.StoredProcedure, sqlParameter);
        }

        public DataTable GetMarketingPaymentJournal(int marketingIncentiveID, int marketingIncentiveSerialID)
        {
            SqlParameter[] sqlParameter = new SqlParameter[2];
            sqlParameter[0] = new SqlParameter("@MarketingIncentiveID", marketingIncentiveID); sqlParameter[0].SqlDbType = SqlDbType.Int; sqlParameter[0].Direction = ParameterDirection.Input;
            sqlParameter[1] = new SqlParameter("@MarketingIncentiveSerialID", marketingIncentiveSerialID); sqlParameter[1].SqlDbType = SqlDbType.Int; sqlParameter[1].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingPaymentJournal", CommandType.StoredProcedure, sqlParameter);
        }



        public DataTable GetMarketingMasterJournal()
        {
            DataTable tableMarketingProgramID = this.GetFilterMarketingProgramID();

            SqlParameter[] sqlParameter = new SqlParameter[1];
            sqlParameter[0] = new SqlParameter("@MarketingProgramIDList", tableMarketingProgramID); sqlParameter[0].SqlDbType = SqlDbType.Structured; sqlParameter[0].TypeName = "dbo.IdentityTable"; sqlParameter[0].Direction = ParameterDirection.Input;

            return SQLDatabase.GetDataTable("MarketingMasterJournal", CommandType.StoredProcedure, sqlParameter);
        }



        #endregion <Storage>

        #region Properties

        #region <Primary Key>

        public int MarketingMonitoringID   //Primary Key
        {
            get { return 0; }
        }

        #endregion <Primary Key>


        private string monitoringOption;
        [DefaultValue("MarketingMonitoringByProgram")]
        public string MonitoringOption
        {
            get { return this.monitoringOption; }
            set { ApplyPropertyChange<MarketingMonitoringBLL, string>(ref this.monitoringOption, o => o.MonitoringOption, value); }
        }


        private DataSet dataSetFilter;
        private DataTable dataTableFilterCommodity;
        private DataTable dataTableFilterCustomer;
        private DataTable dataTableFilterMarketingProgram;

        public DataSet DataSetFilter
        {
            get { return this.dataSetFilter; }
        }

        #endregion Properties





        #region Object Permission

        public bool ReadOnly
        {
            get
            {
                return true;
            }
        }

        public bool Editable
        {
            get
            {
                return false;
            }
        }

        public bool Verifiable
        {
            get
            {
                return false;
            }
        }

        public bool Unverifiable
        {
            get
            {
                return false;
            }
        }


        #endregion Object Permission






        public void Fill(int marketingPaymentID)
        {
        }

        public void New()
        {
        }

        public void Edit()
        {
        }

        #region Save & Delete Method


        public bool Save()
        {
            try
            {
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }


        public bool Delete()
        {
            try
            {
                return true;
            }
            catch (System.Exception exception)
            {
                throw exception;
            }
        }

        #endregion Save & Delete Method



        private void RestoreProcedure()
        {
            string queryString;

            queryString = "     @listBeginingYear varchar(8000), @listBeginingMonth varchar(8000), @listMarketingProgramID varchar(8000) " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";
            queryString = queryString + "       SELECT      MarketingProgramID      FROM    MarketingProgramMaster      WHERE	YEAR(BeginingDate) IN (SELECT IDField FROM FNSplitUpIds(@listBeginingYear)) " + "\r\n";
            queryString = queryString + "       UNION ALL " + "\r\n";
            queryString = queryString + "       SELECT      MarketingProgramID      FROM    MarketingProgramMaster      WHERE	CAST(LEFT(CONVERT(nvarchar(30), BeginingDate, 112), 6) AS int) IN (SELECT IDField FROM FNSplitUpIds(@listBeginingMonth)) " + "\r\n";
            queryString = queryString + "       UNION ALL " + "\r\n";
            queryString = queryString + "       SELECT      MarketingProgramID      FROM    MarketingProgramMaster      WHERE	MarketingProgramID IN (SELECT IDField FROM FNSplitUpIds(@listMarketingProgramID)) " + "\r\n";
            SQLDatabase.CreateStoredProcedure("MarketingYearMonthIDFilter", queryString);




            queryString = "     @LowerFillterDate DateTime, @UpperFillterDate DateTime, @ListItemCategoryID varchar(8000), @ListCommodityID varchar(8000), @ListCustomerChannelID varchar(8000), @MarketingProgramIDList dbo.IdentityTable READONLY " + "\r\n";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            //queryString = queryString + "       DECLARE @ListItemCategoryID varchar(5000) " + "\r\n";
            //queryString = queryString + "       DECLARE @ListCommodityID varchar(5000) " + "\r\n";
            //queryString = queryString + "       DECLARE @ListCustomerChannelID varchar(5000) " + "\r\n";

            //queryString = queryString + "       SET @ListItemCategoryID = '11; 67' " + "\r\n";
            //queryString = queryString + "       SET @ListCommodityID = '7; 8; 9' " + "\r\n";
            //queryString = queryString + "       SET @ListCustomerChannelID = '11; 67' " + "\r\n";

            //queryString = queryString + "       DECLARE @LowerFillterDate DateTime " + "\r\n";
            //queryString = queryString + "       DECLARE @UpperFillterDate DateTime " + "\r\n";

            //queryString = queryString + "       SET @LowerFillterDate = CONVERT(DATETIME, '2012-01-01 00:00:00', 102) " + "\r\n";
            //queryString = queryString + "       SET @UpperFillterDate = CONVERT(DATETIME, '2013-12-31 00:00:00', 102) " + "\r\n";


            queryString = queryString + "       DECLARE @ListMarketingProgramID TABLE (MarketingProgramID int, ItemCategoryID int) " + "\r\n";


            queryString = queryString + "       IF (@ListItemCategoryID <> '' OR @ListCommodityID <> '') " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";

            //==========================================1: ALL CHILDREN (((((LOOKUP CHILDREN DOWN)))))

            //----------------------------------------------1.A: FILTER BY CATEGORY
            queryString = queryString + "               IF @ListItemCategoryID <> '' " + "\r\n";
            queryString = queryString + "                   BEGIN " + "\r\n";


            //--------------------------------------------------1.A.a: ALL CATEGORY + ITS CHILDREN CATEGORY
            queryString = queryString + "                       INSERT INTO @ListMarketingProgramID (MarketingProgramID, ItemCategoryID) " + "\r\n";
            queryString = queryString + "                       SELECT		MarketingProgramID, ItemCategoryID " + "\r\n";

            queryString = queryString + "                       FROM		MarketingProgramItemCategory " + "\r\n";
            queryString = queryString + "                       WHERE		ItemCategoryID IN   ( " + "\r\n";

            queryString = queryString + "                                                       SELECT  ItemCategoryID " + "\r\n";
            queryString = queryString + "                                                       FROM    ListItemCategoryFull " + "\r\n";
            queryString = queryString + "                                                       WHERE   " + "\r\n";
            queryString = queryString + "                                                               ItemCategoryID1 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID2 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID3 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID4 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID5 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID6 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID7 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID8 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";
            queryString = queryString + "                                                               OR ItemCategoryID9 IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID ))  " + "\r\n";

            queryString = queryString + "                                                       ) " + "\r\n";


            //--------------------------------------------------1.A.b: ALL CommodityID BELONG TO [1.A.a]
            queryString = queryString + "                       INSERT INTO @ListMarketingProgramID (MarketingProgramID, ItemCategoryID) " + "\r\n";
            queryString = queryString + "                       SELECT		MarketingProgramItemCommodity.MarketingProgramID, ListItemCommodity.ItemCategoryID " + "\r\n";

            queryString = queryString + "                       FROM		MarketingProgramItemCommodity INNER JOIN ListItemCommodity ON MarketingProgramItemCommodity.CommodityID = ListItemCommodity.CommodityID " + "\r\n";
            queryString = queryString + "                       WHERE		ListItemCommodity.ItemCategoryID IN     ( " + "\r\n";

            queryString = queryString + "                                                                           SELECT  ItemCategoryID " + "\r\n";
            queryString = queryString + "                                                                           FROM    @ListMarketingProgramID " + "\r\n";

            queryString = queryString + "                                                                           ) " + "\r\n";


            queryString = queryString + "                   END " + "\r\n";



            //----------------------------------------------1.B: FILTER BY COMMODITY
            queryString = queryString + "               if @ListCommodityID <> '' " + "\r\n";
            queryString = queryString + "                   BEGIN " + "\r\n";
            queryString = queryString + "                       INSERT INTO @ListMarketingProgramID (MarketingProgramID, ItemCategoryID) " + "\r\n";
            queryString = queryString + "                       SELECT		MarketingProgramID, 0 AS ItemCategoryID " + "\r\n"; //Non't care ItemCategoryID
            queryString = queryString + "                       FROM		MarketingProgramItemCommodity " + "\r\n";
            queryString = queryString + "                       WHERE		CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID )) " + "\r\n";
            queryString = queryString + "                   END " + "\r\n";




            //==========================================2: ALL FATHER (AND GRANDFATHER) CATEGORY OF SELECTED CATEGORY OR COMMODITY  (((((LOOKUP FATHER UP)))))

            queryString = queryString + "               INSERT INTO @ListMarketingProgramID (MarketingProgramID, ItemCategoryID) " + "\r\n";
            queryString = queryString + "               SELECT		MarketingProgramID, ItemCategoryID " + "\r\n";

            queryString = queryString + "               FROM		MarketingProgramItemCategory " + "\r\n";
            queryString = queryString + "               WHERE		ItemCategoryID IN   ( " + "\r\n";

            queryString = queryString + "                                               SELECT      ItemCategoryID1 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID2 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID3 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID4 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID5 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID6 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID7 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID8 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      ItemCategoryID9 FROM ListItemCategoryFull WHERE ItemCategoryID IN (SELECT IDField FROM FNSplitUpIds(@ListItemCategoryID )) OR ItemCategoryID IN (SELECT ItemCategoryID FROM ListItemCommodity WHERE CommodityID IN (SELECT IDField FROM FNSplitUpIds(@ListCommodityID ))) " + "\r\n";

            queryString = queryString + "                                               ) " + "\r\n";

            queryString = queryString + "           END " + "\r\n";









            //==================================FILTER BY DATE
            queryString = queryString + "       IF (@ListItemCategoryID <> '' OR @ListCommodityID <> '') " + "\r\n";
            queryString = queryString + "           DELETE FROM		@ListMarketingProgramID " + "\r\n";
            queryString = queryString + "           WHERE           MarketingProgramID NOT IN  " + "\r\n";
            queryString = queryString + "                           ( " + "\r\n";
            queryString = queryString + "                           SELECT     MarketingProgramID " + "\r\n";
            queryString = queryString + "                           FROM         MarketingProgramMaster " + "\r\n";
            queryString = queryString + "                           WHERE     BeginingDate >= @LowerFillterDate AND BeginingDate <= @UpperFillterDate " + "\r\n";
            queryString = queryString + "                           ) " + "\r\n";


            queryString = queryString + "       ELSE " + "\r\n";
            queryString = queryString + "           INSERT INTO		@ListMarketingProgramID (MarketingProgramID, ItemCategoryID) " + "\r\n";
            queryString = queryString + "           SELECT			MarketingProgramID, 0 AS ItemCategoryID " + "\r\n"; //Non't care ItemCategoryID
            queryString = queryString + "           FROM			MarketingProgramMaster " + "\r\n";
            queryString = queryString + "           WHERE			BeginingDate >= @LowerFillterDate AND BeginingDate <= @UpperFillterDate " + "\r\n";




            //==================================FILTER BY CUSTOMER
            queryString = queryString + "       IF (@ListCustomerChannelID <> '' ) " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";

            queryString = queryString + "               DECLARE @ListMarketingProgramIDByCustomer TABLE (MarketingProgramID int, CustomerChannelID int) " + "\r\n";

            //==========================================1: ALL CHILDREN (((((LOOKUP CHILDREN DOWN)))))

            //------------------------------------------1.A.a: ALL CATEGORY + ITS CHILDREN CATEGORY
            queryString = queryString + "               INSERT INTO @ListMarketingProgramIDByCustomer (MarketingProgramID, CustomerChannelID) " + "\r\n";
            queryString = queryString + "               SELECT		MarketingProgramID, CustomerChannelID " + "\r\n";

            queryString = queryString + "               FROM		MarketingProgramCustomerChannel " + "\r\n";
            queryString = queryString + "               WHERE		CustomerChannelID IN   ( " + "\r\n";

            queryString = queryString + "                                               SELECT  CustomerChannelID " + "\r\n";
            queryString = queryString + "                                               FROM    ListCustomerChannelFull " + "\r\n";
            queryString = queryString + "                                               WHERE   " + "\r\n";
            queryString = queryString + "                                                       CustomerChannelID1 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID2 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID3 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID4 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID5 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID6 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID7 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID8 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";
            queryString = queryString + "                                                       OR CustomerChannelID9 IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID ))  " + "\r\n";

            queryString = queryString + "                                                   ) " + "\r\n";


            //------------------------------------------1.A.b: ALL CustomerID BELONG TO [1.A.a]
            queryString = queryString + "               INSERT INTO @ListMarketingProgramIDByCustomer (MarketingProgramID, CustomerChannelID) " + "\r\n";
            queryString = queryString + "               SELECT		MarketingProgramCustomerName.MarketingProgramID, ListCustomerName.CustomerChannelID " + "\r\n";

            queryString = queryString + "               FROM		MarketingProgramCustomerName INNER JOIN ListCustomerName ON MarketingProgramCustomerName.CustomerID = ListCustomerName.CustomerID " + "\r\n";
            queryString = queryString + "               WHERE		ListCustomerName.CustomerChannelID IN     ( " + "\r\n";

            queryString = queryString + "                                                                   SELECT  CustomerChannelID " + "\r\n";
            queryString = queryString + "                                                                   FROM    @ListMarketingProgramIDByCustomer " + "\r\n";

            queryString = queryString + "                                                                       ) " + "\r\n";


            //==========================================2: ALL FATHER (AND GRANDFATHER) CATEGORY OF SELECTED CATEGORY (((((LOOKUP FATHER UP)))))

            queryString = queryString + "               INSERT INTO @ListMarketingProgramIDByCustomer (MarketingProgramID, CustomerChannelID) " + "\r\n";
            queryString = queryString + "               SELECT		MarketingProgramID, CustomerChannelID " + "\r\n";

            queryString = queryString + "               FROM		MarketingProgramCustomerChannel " + "\r\n";
            queryString = queryString + "               WHERE		CustomerChannelID IN   ( " + "\r\n";

            queryString = queryString + "                                               SELECT      CustomerChannelID1 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID2 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID3 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID4 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID5 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID6 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID7 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID8 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";
            queryString = queryString + "                                               UNION ALL   " + "\r\n";
            queryString = queryString + "                                               SELECT      CustomerChannelID9 FROM ListCustomerChannelFull WHERE CustomerChannelID IN (SELECT IDField FROM FNSplitUpIds(@ListCustomerChannelID )) " + "\r\n";

            queryString = queryString + "                                               ) " + "\r\n";

            //==========================================3: AND FINALLY, REMOVE ANY FROM @ListMarketingProgramID WHICH DOES NOT MATCH IN @ListMarketingProgramIDByCustomer
            queryString = queryString + "               DELETE FROM		@ListMarketingProgramID " + "\r\n";
            queryString = queryString + "               WHERE           MarketingProgramID NOT IN (SELECT MarketingProgramID FROM @ListMarketingProgramIDByCustomer) " + "\r\n";

            queryString = queryString + "           END " + "\r\n";


            //==================================FILTER BY PROGRAM
            queryString = queryString + "       DECLARE MarketingProgram_Cursor CURSOR FOR SELECT IdentityID AS A FROM @MarketingProgramIDList " + "\r\n";
            queryString = queryString + "       OPEN MarketingProgram_Cursor " + "\r\n";
            queryString = queryString + "       DECLARE @IdentityID int   SET @IdentityID = -1 " + "\r\n";
            queryString = queryString + "       FETCH NEXT FROM MarketingProgram_Cursor INTO @IdentityID " + "\r\n";
            queryString = queryString + "       IF @@FETCH_STATUS = 0 AND @IdentityID <> -1 " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "               DELETE FROM		@ListMarketingProgramID " + "\r\n";
            queryString = queryString + "               WHERE           MarketingProgramID NOT IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n";
            queryString = queryString + "           END " + "\r\n";

            queryString = queryString + "       CLOSE MarketingProgram_Cursor " + "\r\n";
            queryString = queryString + "       DEALLOCATE MarketingProgram_Cursor " + "\r\n";




            queryString = queryString + "       SELECT MarketingProgramID FROM @ListMarketingProgramID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingMonitoringFilter", queryString);





            queryString = " @MarketingProgramIDList dbo.IdentityTable READONLY ";
            queryString = queryString + " WITH ENCRYPTION " + "\r\n";
            queryString = queryString + " AS " + "\r\n";

            queryString = queryString + "       IF 1 = 0 BEGIN SET FMTONLY OFF END" + "\r\n"; //To make this stored procedure support typed dataset

            queryString = queryString + "       DECLARE         @CustomerID int SET @CustomerID = 0 " + "\r\n";
            queryString = queryString + "       DECLARE         @columnsPivot VARCHAR(8000)" + "\r\n";
            queryString = queryString + "       DECLARE         @columnsDefine VARCHAR(8000)" + "\r\n";
            queryString = queryString + "       DECLARE         @columnsUpdateNULL VARCHAR(8000) " + "\r\n";

            queryString = queryString + "       CREATE TABLE    #PivotTableApplyItem (ApplyItemMarketingProgramID int)  " + "\r\n";
            queryString = queryString + "       CREATE TABLE    #PivotTableApplyChannel (ApplyChannelMarketingProgramID int)  " + "\r\n";

            queryString = queryString + "                       " + this.PivotApplyItem(false) + "\r\n";
            queryString = queryString + "                       " + this.PivotApplyChannel(false) + "\r\n";
            queryString = queryString + "                       " + this.PivotPaymentTerm(true, false) + "\r\n";
            queryString = queryString + "                       " + this.PivotPaymentPeriod(true, false) + "\r\n";


            queryString = queryString + "       SELECT          ListMarketingProgramType.Description AS [Program Type], MarketingProgramMaster.MarketingProgramID, MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode AS [Reference Code],  " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster.MarketingProgramName AS [Program Name], MarketingProgramMaster.MarketingProgramSubName AS [Sub Name], ListMarketingProgramGroup.Description AS [Program Group], ListCustomerChannel.Description AS [Customer Channel], ListTimeBase.Description AS [Time Base],  " + "\r\n";
            queryString = queryString + "                       LEFT(CONVERT(char(10), MarketingProgramMaster.BeginingDate, 111), 7) AS [Begining Month], MarketingProgramMaster.BeginingDate AS [Begining Date], MarketingProgramMaster.EndingDate AS [Ending Date], " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster.SalesTarget AS [Sales Target], MarketingProgramMaster.MarketingBudget AS [Budget], MarketingPaymentSummary.TurnOverAmount AS [Sales Amount], CASE WHEN MarketingProgramMaster.SalesTarget <> 0 THEN (ISNULL(MarketingPaymentSummary.TurnOverAmount, 0)/ MarketingProgramMaster.SalesTarget) * 100 ELSE 0 END AS [% Performance], MarketingPaymentSummary.IncentiveAmount AS [Incentive Amount], CASE WHEN MarketingProgramMaster.MarketingBudget <> 0 THEN (ISNULL(MarketingPaymentSummary.IncentiveAmount, 0)/ MarketingProgramMaster.MarketingBudget) * 100 ELSE 0 END AS [% Budget], #PivotTablePaymentTermIncentive.*, #PivotTablePaymentPeriod.*, " + "\r\n";
            queryString = queryString + "                       MarketingPaymentSummary.PaidAmount AS [Amount Paid], MarketingPaymentSummary.BalanceAmount AS [Balance], #PivotTableApplyChannel.*, #PivotTableApplyItem.* " + "\r\n";

            queryString = queryString + "       FROM            MarketingProgramMaster INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramGroup ON MarketingProgramMaster.MarketingProgramGroupID = ListMarketingProgramGroup.MarketingProgramGroupID AND MarketingProgramMaster.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramType ON MarketingProgramMaster.MarketingProgramTypeID = ListMarketingProgramType.MarketingProgramTypeID INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListCustomerChannel ON MarketingProgramMaster.CustomerChannelID = ListCustomerChannel.CustomerChannelID INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListTimeBase ON MarketingProgramMaster.TimeBaseID = ListTimeBase.TimeBaseID LEFT JOIN  " + "\r\n";

            queryString = queryString + "                      (SELECT PaymentTermMarketingProgramID, SUM(TurnOverAmount) AS TurnOverAmount, SUM(IncentiveAmount) AS IncentiveAmount, SUM(PaidAmount) AS PaidAmount, SUM(BalanceAmount) AS BalanceAmount  FROM #CubeTablePaymentTerm WHERE PaymentTermMarketingProgramID <> 0 AND MarketingPaymentTerm <> 'ZTotal' GROUP BY PaymentTermMarketingProgramID) AS MarketingPaymentSummary ON MarketingProgramMaster.MarketingProgramID = MarketingPaymentSummary.PaymentTermMarketingProgramID LEFT JOIN " + "\r\n";

            queryString = queryString + "                       #PivotTableApplyItem ON MarketingProgramMaster.MarketingProgramID = #PivotTableApplyItem.ApplyItemMarketingProgramID LEFT JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTableApplyChannel ON MarketingProgramMaster.MarketingProgramID = #PivotTableApplyChannel.ApplyChannelMarketingProgramID LEFT JOIN " + "\r\n";

            queryString = queryString + "                       #PivotTablePaymentPeriod ON MarketingProgramMaster.MarketingProgramID = #PivotTablePaymentPeriod.PaymentPeriodMarketingProgramID LEFT JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTablePaymentTermIncentive ON MarketingProgramMaster.MarketingProgramID = #PivotTablePaymentTermIncentive.PaymentTermIncentiveMarketingProgramID " + "\r\n";

            queryString = queryString + "       DROP TABLE      #PivotTablePaymentTermIncentive " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentTerm " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTableApplyItem " + "\r\n";
            queryString = queryString + "       DROP TABLE      #ApplyItemCubeTable " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTableApplyChannel " + "\r\n";
            queryString = queryString + "       DROP TABLE      #ApplyChannelCubeTable " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingMonitoringByProgram", queryString);



            queryString = " @MarketingProgramIDList dbo.IdentityTable READONLY " + "\r\n";

            queryString = queryString + " WITH ENCRYPTION  " + "\r\n";
            queryString = queryString + " AS  " + "\r\n";

            queryString = queryString + "       IF 1=0 BEGIN SET FMTONLY OFF END " + "\r\n";

            queryString = queryString + "       DECLARE         @columnsPivot VARCHAR(8000) " + "\r\n";
            queryString = queryString + "       DECLARE         @columnsDefine VARCHAR(8000) " + "\r\n";
            queryString = queryString + "       DECLARE         @columnsUpdateNULL VARCHAR(8000) " + "\r\n";

            queryString = queryString + PivotPaymentTerm(false, true) + "\r\n";
            queryString = queryString + PivotPaymentPeriod(false, true) + "\r\n";

            queryString = queryString + "       SELECT			ListAddressArea.Description AS [Territory], MarketingPaymentSummary.CustomerID, ListCustomerName.Description AS [Customer Code], ListCustomerName.DescriptionOfficial AS [Customer Name], MarketingPaymentSummary.TurnOverAmount AS [Sales Amount], MarketingPaymentSummary.IncentiveAmount AS [Incentive Amount], #PivotTablePaymentTermIncentive.*, #PivotTablePaymentPeriod.*, MarketingPaymentSummary.PaidAmount AS [Amount Paid], MarketingPaymentSummary.BalanceAmount AS [Balance] " + "\r\n";

            queryString = queryString + "       FROM		   (SELECT CustomerID, SUM(TurnOverAmount) AS TurnOverAmount, SUM(IncentiveAmount) AS IncentiveAmount, SUM(PaidAmount) AS PaidAmount, SUM(BalanceAmount) AS BalanceAmount  FROM #CubeTablePaymentTerm WHERE CustomerID <> 0 AND MarketingPaymentTerm <> 'ZTotal' GROUP BY CustomerID) AS MarketingPaymentSummary INNER JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTablePaymentPeriod ON MarketingPaymentSummary.CustomerID = #PivotTablePaymentPeriod.CustomerID INNER JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTablePaymentTermIncentive ON #PivotTablePaymentPeriod.CustomerID = #PivotTablePaymentTermIncentive.CustomerID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListCustomerName ON #PivotTablePaymentPeriod.CustomerID = ListCustomerName.CustomerID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID " + "\r\n";

            queryString = queryString + "       DROP TABLE      #PivotTablePaymentTermIncentive " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentTerm " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentPeriod " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingMonitoringByCustomer", queryString);




            queryString = " @MarketingProgramIDList dbo.IdentityTable READONLY, @CustomerID int, @IncludeApplyItem bit " + "\r\n";

            queryString = queryString + " WITH ENCRYPTION  " + "\r\n";
            queryString = queryString + " AS  " + "\r\n";

            queryString = queryString + "       IF 1=0 BEGIN SET FMTONLY OFF END " + "\r\n";

            queryString = queryString + "       DECLARE         @columnsPivot VARCHAR(8000) " + "\r\n";
            queryString = queryString + "       DECLARE         @columnsDefine VARCHAR(8000) " + "\r\n";
            queryString = queryString + "       DECLARE         @columnsUpdateNULL VARCHAR(8000) " + "\r\n";

            queryString = queryString + PivotPaymentTerm(true, true) + "\r\n";
            queryString = queryString + PivotPaymentPeriod(true, true) + "\r\n";

            queryString = queryString + "       CREATE TABLE    #PivotTableApplyItem (ApplyItemMarketingProgramID int) " + "\r\n";
            queryString = queryString + "       CREATE TABLE    #PivotTableApplyChannel (ApplyChannelMarketingProgramID int) " + "\r\n";

            queryString = queryString + "       IF @IncludeApplyItem = 1 " + "\r\n"; //PIVOT MarketingProgramMaster BY ApplyItem
            queryString = queryString + "       BEGIN " + "\r\n";
            queryString = queryString + "                       " + this.PivotApplyItem(true) + "\r\n";
            queryString = queryString + "                       " + this.PivotApplyChannel(true) + "\r\n";

            queryString = queryString + "       END " + "\r\n";


            queryString = queryString + "       SELECT			ListMarketingProgramType.Description AS [Program Type], MarketingProgramMaster.MarketingProgramID, MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode AS [Reference Code],  " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster.MarketingProgramName AS [Program Name], MarketingProgramMaster.MarketingProgramSubName AS [Sub Name], ListMarketingProgramGroup.Description AS [Program Group], " + "\r\n";
            queryString = queryString + "                       LEFT(DATENAME(Month, MarketingProgramMaster.BeginingDate), 3) + ', ' + DATENAME(Year, MarketingProgramMaster.BeginingDate) AS [Begining Month], MarketingProgramMaster.BeginingDate AS [Begining Date], MarketingProgramMaster.EndingDate AS [Ending Date], ListCustomerName.CustomerID, ListCustomerName.Description AS [Customer Code], ListCustomerName.DescriptionOfficial AS [Customer Name], " + "\r\n";
            queryString = queryString + "                       MarketingPaymentSummary.TurnOverAmount AS [Sales Amount], MarketingPaymentSummary.IncentiveAmount AS [Incentive Amount], #PivotTablePaymentTermIncentive.*, #PivotTablePaymentPeriod.*, " + "\r\n";
            queryString = queryString + "                       MarketingPaymentSummary.PaidAmount AS [Amount Paid], MarketingPaymentSummary.BalanceAmount AS [Balance], #PivotTableApplyChannel.*, #PivotTableApplyItem.* " + "\r\n";

            queryString = queryString + "       FROM		   (SELECT PaymentTermMarketingProgramID, CustomerID, SUM(TurnOverAmount) AS TurnOverAmount, SUM(IncentiveAmount) AS IncentiveAmount, SUM(PaidAmount) AS PaidAmount, SUM(BalanceAmount) AS BalanceAmount  FROM #CubeTablePaymentTerm WHERE PaymentTermMarketingProgramID <> 0 AND CustomerID <> 0 AND MarketingPaymentTerm <> 'ZTotal' GROUP BY PaymentTermMarketingProgramID, CustomerID) AS MarketingPaymentSummary INNER JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTablePaymentPeriod ON MarketingPaymentSummary.PaymentTermMarketingProgramID = #PivotTablePaymentPeriod.PaymentPeriodMarketingProgramID AND MarketingPaymentSummary.CustomerID = #PivotTablePaymentPeriod.CustomerID INNER JOIN " + "\r\n";

            queryString = queryString + "                       #PivotTablePaymentTermIncentive ON #PivotTablePaymentPeriod.PaymentPeriodMarketingProgramID = #PivotTablePaymentTermIncentive.PaymentTermIncentiveMarketingProgramID AND #PivotTablePaymentPeriod.CustomerID = #PivotTablePaymentTermIncentive.CustomerID INNER JOIN " + "\r\n";

            queryString = queryString + "                       MarketingProgramMaster ON MarketingPaymentSummary.PaymentTermMarketingProgramID = MarketingProgramMaster.MarketingProgramID INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramGroup ON MarketingProgramMaster.MarketingProgramGroupID = ListMarketingProgramGroup.MarketingProgramGroupID INNER JOIN  " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramType ON MarketingProgramMaster.MarketingProgramTypeID = ListMarketingProgramType.MarketingProgramTypeID INNER JOIN  " + "\r\n";

            queryString = queryString + "                       ListCustomerName ON MarketingPaymentSummary.CustomerID = ListCustomerName.CustomerID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListAddressArea ON ListCustomerName.AddressAreaID = ListAddressArea.AddressAreaID LEFT JOIN " + "\r\n";

            queryString = queryString + "                       #PivotTableApplyItem ON #PivotTablePaymentPeriod.PaymentPeriodMarketingProgramID = #PivotTableApplyItem.ApplyItemMarketingProgramID LEFT JOIN " + "\r\n";
            queryString = queryString + "                       #PivotTableApplyChannel ON #PivotTablePaymentPeriod.PaymentPeriodMarketingProgramID = #PivotTableApplyChannel.ApplyChannelMarketingProgramID " + "\r\n";

            queryString = queryString + "       DROP TABLE      #PivotTablePaymentTermIncentive " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentTerm " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       DROP TABLE      #CubeTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTableApplyItem " + "\r\n";
            queryString = queryString + "       DROP TABLE      #ApplyItemCubeTable " + "\r\n";
            queryString = queryString + "       DROP TABLE      #PivotTableApplyChannel " + "\r\n";
            queryString = queryString + "       DROP TABLE      #ApplyChannelCubeTable " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingMonitoringByPayment", queryString);



            queryString = " @MarketingProgramID int, @CustomerID int " + "\r\n";

            queryString = queryString + " WITH ENCRYPTION  " + "\r\n";
            queryString = queryString + " AS  " + "\r\n";

            queryString = queryString + "       SELECT          MarketingIncentiveDetail.MarketingIncentiveID, MarketingIncentiveDetail.SerialID AS MarketingIncentiveSerialID, MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.PaymentPeriod, ListMarketingPaymentType.Description AS MarketingPaymentTypeDescription, ListMarketingPaymentTerm.Description AS MarketingPaymentTermDescription, MarketingIncentiveDetail.Remarks, " + "\r\n";
            queryString = queryString + "                       MarketingIncentiveDetail.TurnOverAmount, MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, ROUND(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount, 0) AS BalanceAmount " + "\r\n";
            queryString = queryString + "       FROM            MarketingIncentiveDetail INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentType ON MarketingIncentiveDetail.MarketingPaymentTypeID = ListMarketingPaymentType.MarketingPaymentTypeID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentTerm ON MarketingIncentiveDetail.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID " + "\r\n";
            queryString = queryString + "       WHERE           MarketingIncentiveDetail.MarketingProgramID = @MarketingProgramID AND MarketingIncentiveDetail.CustomerID = @CustomerID " + "\r\n";
            queryString = queryString + "       ORDER BY        MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.MarketingIncentiveID " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingIncentiveJournal", queryString);



            queryString = " @MarketingIncentiveID int, @MarketingIncentiveSerialID int " + "\r\n";

            queryString = queryString + " WITH ENCRYPTION  " + "\r\n";
            queryString = queryString + " AS  " + "\r\n";

            queryString = queryString + "       SELECT          MarketingPaymentMaster.PaymentDate, MarketingPaymentDetail.PaymentAmount, MarketingPaymentDetail.Remarks " + "\r\n";
            queryString = queryString + "       FROM            MarketingPaymentDetail INNER JOIN MarketingPaymentMaster ON MarketingPaymentDetail.MarketingPaymentID = MarketingPaymentMaster.MarketingPaymentID " + "\r\n";
            queryString = queryString + "       WHERE           MarketingPaymentDetail.MarketingIncentiveID = @MarketingIncentiveID AND MarketingPaymentDetail.MarketingIncentiveSerialID = @MarketingIncentiveSerialID " + "\r\n";
            queryString = queryString + "       ORDER BY        MarketingPaymentMaster.PaymentDate " + "\r\n";

            SQLDatabase.CreateStoredProcedure("MarketingPaymentJournal", queryString);


            

            SQLDatabase.CreateView("VWMarketingMasterJournal", this.QueryStringMarketingMasterJournal(false));
            SQLDatabase.CreateStoredProcedure("MarketingMasterJournal", " @MarketingProgramIDList dbo.IdentityTable READONLY " + "\r\n" + " WITH ENCRYPTION  " + "\r\n" + " AS  " + "\r\n" + this.QueryStringMarketingMasterJournal(true));

        }


        private string PivotApplyItem(Boolean removeUnApplyItem)
        {
            string queryString;

            queryString = "                   SELECT        ApplyItemMarketingProgramID, ApplyItem, 1 AS HasApply " + "\r\n";
            queryString = queryString + "     INTO		    #ApplyItemCubeTable " + "\r\n";
            queryString = queryString + "     FROM	        ( " + "\r\n";
            queryString = queryString + "                   SELECT     MarketingProgramItemCategory.MarketingProgramID AS ApplyItemMarketingProgramID, ListItemCategoryFull.Description3 AS ApplyItem FROM         MarketingProgramItemCategory INNER JOIN ListItemCategoryFull ON MarketingProgramItemCategory.ItemCategoryID <> 0 AND MarketingProgramItemCategory.ItemCategoryID = ListItemCategoryFull.ItemCategoryID3 WHERE MarketingProgramItemCategory.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n"; //Apply BRAND => Get BRAND
            queryString = queryString + "                   UNION ALL  " + "\r\n";
            queryString = queryString + "                   SELECT     MarketingProgramItemCategory.MarketingProgramID AS ApplyItemMarketingProgramID, ListItemCategoryFull.Description3 AS ApplyItem FROM         MarketingProgramItemCategory INNER JOIN ListItemCategoryFull ON MarketingProgramItemCategory.ItemCategoryID <> 0 AND MarketingProgramItemCategory.ItemCategoryID = ListItemCategoryFull.ItemCategoryID2 AND ListItemCategoryFull.ItemCategoryID3 <> 0 WHERE MarketingProgramItemCategory.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n"; //Apply GROUP => Get ALL BRAND belong to GROUP
            queryString = queryString + "                   UNION ALL  " + "\r\n";

            queryString = queryString + "                   SELECT     MarketingProgramItemCommodity.MarketingProgramID AS ApplyItemMarketingProgramID, ListItemCategoryFull.Description3 AS ApplyItem FROM         MarketingProgramItemCommodity INNER JOIN ListItemCommodity ON MarketingProgramItemCommodity.CommodityID = ListItemCommodity.CommodityID INNER JOIN ListItemCategoryFull ON ListItemCommodity.ItemCategoryID = ListItemCategoryFull.ItemCategoryID3 WHERE MarketingProgramItemCommodity.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n"; //Apply BRAND => Get BRAND
            queryString = queryString + "                   UNION ALL  " + "\r\n";
            queryString = queryString + "                   SELECT     MarketingProgramItemCommodity.MarketingProgramID AS ApplyItemMarketingProgramID, ListItemCategoryFull.Description3 AS ApplyItem FROM         MarketingProgramItemCommodity INNER JOIN ListItemCommodity ON MarketingProgramItemCommodity.CommodityID = ListItemCommodity.CommodityID INNER JOIN ListItemCategoryFull ON ListItemCommodity.ItemCategoryID = ListItemCategoryFull.ItemCategoryID2 AND ListItemCategoryFull.ItemCategoryID3 <> 0 WHERE MarketingProgramItemCommodity.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n";//Apply GROUP => Get ALL BRAND belong to GROUP
            queryString = queryString + "                   UNION ALL  " + "\r\n";

            queryString = queryString + "                   SELECT     MarketingProgramMaster.MarketingProgramID AS ApplyItemMarketingProgramID, ListItemCategory.Description AS ApplyItem FROM         MarketingProgramMaster CROSS JOIN ListItemCategory WHERE ListItemCategory.HierarchyLevel = 3 AND MarketingProgramMaster.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) AND MarketingProgramMaster.MarketingProgramID NOT IN (SELECT MarketingProgramID FROM MarketingProgramItemCategory WHERE ItemCategoryID <> 0 UNION ALL SELECT MarketingProgramID FROM MarketingProgramItemCommodity WHERE CommodityID <> 0) " + "\r\n";//Apply ALL => Get ALL BRAND
            queryString = queryString + "                   ) ApplyItemCollection " + "\r\n";

            if (removeUnApplyItem)
                queryString = queryString + "               DELETE FROM #ApplyItemCubeTable WHERE ApplyItemMarketingProgramID NOT IN (SELECT PaymentPeriodMarketingProgramID FROM #PivotTablePaymentPeriod)  " + "\r\n"; //REMOVE UN-APPLY ITEM


            queryString = queryString + "     SELECT        @columnsPivot = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([ApplyItem] as varchar) + ']' FROM    #ApplyItemCubeTable ORDER BY ',[' + cast([ApplyItem] as varchar) + ']' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";
            queryString = queryString + "     SELECT        @columnsDefine = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([ApplyItem] as varchar) + '] bit' FROM    #ApplyItemCubeTable ORDER BY ',[' + cast([ApplyItem] as varchar) + '] bit' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";


            queryString = queryString + "     IF    @columnsDefine <> '' " + "\r\n"; //At least a row found
            queryString = queryString + "           BEGIN   " + "\r\n";
            queryString = queryString + "                   EXECUTE('ALTER TABLE #PivotTableApplyItem ADD ' + @columnsDefine ) " + "\r\n";
            queryString = queryString + "                   EXECUTE('INSERT INTO #PivotTableApplyItem SELECT * FROM #ApplyItemCubeTable PIVOT(MIN(HasApply) FOR [ApplyItem] IN (' + @columnsPivot + ') ) AS PivotColumnIncentive ')    " + "\r\n";

            queryString = queryString + "                   SELECT        @columnsUpdateNULL = ( SELECT DISTINCT TOP 100 PERCENT ' UPDATE #PivotTableApplyItem SET [' + cast([ApplyItem] as varchar) + '] = 0 WHERE [' + cast([ApplyItem] as varchar) + ']  IS NULL ' FROM    #ApplyItemCubeTable WHERE [ApplyItem] < 'Obimin' FOR XML PATH('') )  " + "\r\n";
            queryString = queryString + "                   EXECUTE       (@columnsUpdateNULL) " + "\r\n";
            queryString = queryString + "                   SELECT        @columnsUpdateNULL = ( SELECT DISTINCT TOP 100 PERCENT ' UPDATE #PivotTableApplyItem SET [' + cast([ApplyItem] as varchar) + '] = 0 WHERE [' + cast([ApplyItem] as varchar) + ']  IS NULL ' FROM    #ApplyItemCubeTable WHERE [ApplyItem] >= 'Obimin' FOR XML PATH('') )  " + "\r\n";
            queryString = queryString + "                   EXECUTE       (@columnsUpdateNULL) " + "\r\n";


            queryString = queryString + "           END     " + "\r\n";

            return queryString;
        }


        private string PivotApplyChannel(Boolean removeUnApplyChannel)
        {
            string queryString;

            queryString = "                   SELECT        ApplyChannelMarketingProgramID, ApplyChannel, 1 AS HasApply " + "\r\n";
            queryString = queryString + "     INTO		    #ApplyChannelCubeTable " + "\r\n";
            queryString = queryString + "     FROM	        ( " + "\r\n";
            queryString = queryString + "                   SELECT     MarketingProgramCustomerChannel.MarketingProgramID AS ApplyChannelMarketingProgramID, ListCustomerChannel.Description AS ApplyChannel FROM         MarketingProgramCustomerChannel INNER JOIN ListCustomerChannel ON MarketingProgramCustomerChannel.CustomerChannelID = ListCustomerChannel.CustomerChannelID WHERE MarketingProgramCustomerChannel.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n";
            queryString = queryString + "                   UNION ALL  " + "\r\n";
            queryString = queryString + "                   SELECT     MarketingProgramMaster.MarketingProgramID AS ApplyChannelMarketingProgramID, 'All Channels' AS ApplyChannel FROM         MarketingProgramMaster WHERE MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) AND MarketingProgramID NOT IN (SELECT MarketingProgramID FROM MarketingProgramCustomerChannel UNION ALL SELECT MarketingProgramID FROM MarketingProgramItemCommodity) " + "\r\n";
            queryString = queryString + "                   ) ApplyChannelCollection " + "\r\n";

            if (removeUnApplyChannel)
                queryString = queryString + "               DELETE FROM #ApplyChannelCubeTable WHERE ApplyChannelMarketingProgramID NOT IN (SELECT PaymentPeriodMarketingProgramID FROM #PivotTablePaymentPeriod)  " + "\r\n"; //REMOVE UN-APPLY CHANNEL

            queryString = queryString + "     SELECT        @columnsPivot = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([ApplyChannel] as varchar) + ']' FROM    #ApplyChannelCubeTable ORDER BY ',[' + cast([ApplyChannel] as varchar) + ']' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";
            queryString = queryString + "     SELECT        @columnsDefine = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([ApplyChannel] as varchar) + '] bit' FROM    #ApplyChannelCubeTable ORDER BY ',[' + cast([ApplyChannel] as varchar) + '] bit' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";


            queryString = queryString + "     IF    @columnsDefine <> '' " + "\r\n"; //Atleast a row found
            queryString = queryString + "           BEGIN   " + "\r\n";
            queryString = queryString + "                   EXECUTE('ALTER TABLE #PivotTableApplyChannel ADD ' + @columnsDefine ) " + "\r\n";
            queryString = queryString + "                   EXECUTE('INSERT INTO #PivotTableApplyChannel SELECT * FROM #ApplyChannelCubeTable PIVOT(MIN(HasApply) FOR [ApplyChannel] IN (' + @columnsPivot + ') ) AS PivotColumnIncentive ')    " + "\r\n";

            queryString = queryString + "                   SELECT        @columnsUpdateNULL = ( SELECT DISTINCT TOP 100 PERCENT ' UPDATE #PivotTableApplyChannel SET [' + cast([ApplyChannel] as varchar) + '] = 0 WHERE [' + cast([ApplyChannel] as varchar) + ']  IS NULL ' FROM    #ApplyChannelCubeTable FOR XML PATH('') )  " + "\r\n";
            queryString = queryString + "                   EXECUTE       (@columnsUpdateNULL) " + "\r\n";


            queryString = queryString + "           END     " + "\r\n";

            return queryString;
        }

        private string PivotPaymentTerm(Boolean includeMarketingProgramID, Boolean includeCustomerID)
        {
            string queryString;

            queryString = "                     SELECT                  " + "\r\n";
            queryString = queryString + (includeMarketingProgramID ? "  CASE WHEN GROUPING(MarketingIncentiveDetail.MarketingProgramID) = 1 THEN  0 ELSE MarketingIncentiveDetail.MarketingProgramID END AS PaymentTermMarketingProgramID, " : "") + "\r\n";
            queryString = queryString + (includeCustomerID ? "          CASE WHEN GROUPING(MarketingIncentiveDetail.CustomerID) = 1 THEN  0 ELSE MarketingIncentiveDetail.CustomerID END AS CustomerID, " : "") + "\r\n";
            queryString = queryString + "                               CASE WHEN GROUPING(ListMarketingPaymentTerm.Description) = 1 THEN  'ZTotal' ELSE ListMarketingPaymentTerm.Description END AS MarketingPaymentTerm, " + "\r\n";
            queryString = queryString + "                               SUM(MarketingIncentiveDetail.TurnOverAmount) AS TurnOverAmount, SUM(MarketingIncentiveDetail.IncentiveAmount) AS IncentiveAmount,  " + "\r\n";
            queryString = queryString + "                               SUM(MarketingIncentiveDetail.PaidAmount) AS PaidAmount, SUM(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount) AS BalanceAmount " + "\r\n";

            queryString = queryString + "       INTO		            #CubeTablePaymentTerm " + "\r\n";
            queryString = queryString + "       FROM                    MarketingIncentiveDetail INNER JOIN ListMarketingPaymentTerm ON MarketingIncentiveDetail.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID " + "\r\n";
            queryString = queryString + "       WHERE		            " + (includeMarketingProgramID ? " (@CustomerID = 0 OR MarketingIncentiveDetail.CustomerID = @CustomerID) AND " : "") + " MarketingIncentiveDetail.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n";

            queryString = queryString + "       GROUP BY 	            " + (includeMarketingProgramID ? " MarketingIncentiveDetail.MarketingProgramID," : "") + (includeCustomerID ? " MarketingIncentiveDetail.CustomerID, " : "") + " ListMarketingPaymentTerm.Description " + "\r\n";


            queryString = queryString + "       SELECT                  @columnsPivot = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([MarketingPaymentTerm] as varchar) + ']' FROM    #CubeTablePaymentTerm ORDER BY ',[' + cast([MarketingPaymentTerm] as varchar) + ']' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";
            queryString = queryString + "       SELECT                  @columnsDefine = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([MarketingPaymentTerm] as varchar) + '] float' FROM    #CubeTablePaymentTerm ORDER BY ',[' + cast([MarketingPaymentTerm] as varchar) + '] float' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";

            queryString = queryString + "       CREATE TABLE            #PivotTablePaymentTermIncentive (" + (includeMarketingProgramID ? "PaymentTermIncentiveMarketingProgramID int" : "") + (includeMarketingProgramID && includeCustomerID ? ", " : "") + (includeCustomerID ? " CustomerID int " : "") + ") " + "\r\n";

            queryString = queryString + "       IF @columnsDefine <> '' " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "                               EXECUTE('ALTER TABLE #PivotTablePaymentTermIncentive ADD ' + @columnsDefine ) " + "\r\n";

            queryString = queryString + "                               EXECUTE('INSERT INTO #PivotTablePaymentTermIncentive SELECT * FROM ( SELECT " + (includeMarketingProgramID ? "PaymentTermMarketingProgramID AS PaymentTermIncentiveMarketingProgramID, " : "") + (includeCustomerID ? " CustomerID, " : "") + " MarketingPaymentTerm, IncentiveAmount FROM #CubeTablePaymentTerm) AS PivotTableIncentive PIVOT(SUM(IncentiveAmount) FOR [MarketingPaymentTerm] IN (' + @columnsPivot + ') ) AS PivotColumnIncentive ') " + "\r\n";

            queryString = queryString + "           END " + "\r\n";

            return queryString;
        }

        private string PivotPaymentPeriod(Boolean includeMarketingProgramID, Boolean includeCustomerID)
        {
            string queryString;

            queryString = "                     SELECT                  " + "\r\n";

            queryString = queryString + (includeMarketingProgramID ? "  CASE WHEN GROUPING(MarketingIncentiveDetail.MarketingProgramID) = 1 THEN  0 ELSE MarketingIncentiveDetail.MarketingProgramID END AS PaymentPeriodMarketingProgramID, " : "") + "\r\n";
            queryString = queryString + (includeCustomerID ? "          CASE WHEN GROUPING(MarketingIncentiveDetail.CustomerID) = 1 THEN  0 ELSE MarketingIncentiveDetail.CustomerID END AS CustomerID, " : "") + "\r\n";

            queryString = queryString + "                               CASE WHEN GROUPING(MarketingIncentiveDetail.PaymentPeriod) = 1 THEN  'ZTotal' ELSE MarketingIncentiveDetail.PaymentPeriod END AS PaymentPeriod, " + "\r\n";
            queryString = queryString + "                               SUM(MarketingIncentiveDetail.TurnOverAmount) AS TurnOverAmount, SUM(MarketingIncentiveDetail.IncentiveAmount) AS IncentiveAmount,  " + "\r\n";
            queryString = queryString + "                               SUM(MarketingIncentiveDetail.PaidAmount) AS PaidAmount, SUM(MarketingIncentiveDetail.IncentiveAmount - MarketingIncentiveDetail.PaidAmount) AS BalanceAmount " + "\r\n";

            queryString = queryString + "       INTO		            #CubeTablePaymentPeriod " + "\r\n";
            queryString = queryString + "       FROM                    MarketingIncentiveDetail " + "\r\n";
            queryString = queryString + "       WHERE		            " + (includeMarketingProgramID ? " (@CustomerID = 0 OR MarketingIncentiveDetail.CustomerID = @CustomerID) AND " : "") + " MarketingIncentiveDetail.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " + "\r\n";

            queryString = queryString + "       GROUP BY                " + (includeMarketingProgramID ? " MarketingIncentiveDetail.MarketingProgramID," : "") + (includeCustomerID ? " MarketingIncentiveDetail.CustomerID, " : "") + " MarketingIncentiveDetail.PaymentPeriod " + "\r\n";

            queryString = queryString + "       SELECT                  @columnsPivot = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([PaymentPeriod] as varchar) + ']' FROM    #CubeTablePaymentPeriod ORDER BY ',[' + cast([PaymentPeriod] as varchar) + ']' FOR XML PATH('') ), 1, 1, '')  " + "\r\n";
            queryString = queryString + "       SELECT                  @columnsDefine = STUFF(( SELECT DISTINCT TOP 100 PERCENT ',[' + cast([PaymentPeriod] as varchar) + '] float' FROM    #CubeTablePaymentPeriod ORDER BY ',[' + cast([PaymentPeriod] as varchar) + '] float' FOR XML PATH('') ), 1, 1, '') " + "\r\n";
            queryString = queryString + "       CREATE TABLE            #PivotTablePaymentPeriod (" + (includeMarketingProgramID ? "PaymentPeriodMarketingProgramID int" : "") + (includeMarketingProgramID && includeCustomerID ? ", " : "") + (includeCustomerID ? " CustomerID int " : "") + ") " + "\r\n";
            queryString = queryString + "       IF @columnsDefine <> '' " + "\r\n";
            queryString = queryString + "           BEGIN " + "\r\n";
            queryString = queryString + "                               EXECUTE('ALTER TABLE #PivotTablePaymentPeriod ADD ' + @columnsDefine ) " + "\r\n";

            queryString = queryString + "                               EXECUTE('INSERT INTO #PivotTablePaymentPeriod SELECT * FROM ( SELECT " + (includeMarketingProgramID ? "PaymentPeriodMarketingProgramID, " : "") + (includeCustomerID ? " CustomerID, " : "") + " PaymentPeriod, IncentiveAmount FROM #CubeTablePaymentPeriod) AS PivotTableIncentive PIVOT(SUM(IncentiveAmount) FOR [PaymentPeriod] IN (' + @columnsPivot + ') ) AS PivotColumnIncentive ') " + "\r\n";

            queryString = queryString + "           END " + "\r\n";

            return queryString;
        }


        private string QueryStringMarketingMasterJournal(bool isStoredProcedure)
        {
            string queryString = "";

            queryString = "                     SELECT          MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode, " + "\r\n"; //MarketingProgramMaster.MarketingProgramID, 
            queryString = queryString + "                       MarketingProgramMaster.MarketingProgramName, MarketingProgramMaster.MarketingProgramSubName, ListMarketingProgramGroup.Description AS ProgramGroup, " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramType.Description AS ProgramType, ListCustomerChannel.Description AS CustomerTag, ListTimeBase.Description AS TimeBase, " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster.BeginingDate, MarketingProgramMaster.EndingDate, MarketingProgramMaster.SalesTarget AS TotalSalesTarget, " + "\r\n";
            queryString = queryString + "                       MarketingProgramMaster.MarketingBudget AS TotalMarketingBudget, MarketingProgramMaster.Remarks, " + "\r\n";
            queryString = queryString + "                       ListCustomerName.Description AS CustomerCode, ListCustomerName.DescriptionOfficial AS CustomerName, MarketingProgramCollection.EffectedDate, MarketingProgramCollection.SalesTarget AS CustomerSalesTarget, MarketingProgramCollection.Remarks AS CustomerRemarks, MarketingProgramCollection.RequestedDate, ListMarketingPaymentType.Description AS PaymentTypeName, ListMarketingPaymentTerm.Description AS PaymentTermName, " + "\r\n";
            queryString = queryString + "                       MarketingProgramCollection.PaymentPeriod, MarketingProgramCollection.TurnOverAmount AS SalesAmount, MarketingProgramCollection.IncentiveAmount, MarketingProgramCollection.PaidAmount, MarketingProgramCollection.Description AS IncentiveDescription, MarketingProgramCollection.IncentiveRemarks " + "\r\n";

            queryString = queryString + "       FROM            MarketingProgramMaster INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramGroup ON MarketingProgramMaster.MarketingProgramGroupID = ListMarketingProgramGroup.MarketingProgramGroupID " + (isStoredProcedure ? " AND MarketingProgramMaster.MarketingProgramID IN (SELECT IdentityID FROM @MarketingProgramIDList) " : "") + " INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingProgramType ON MarketingProgramMaster.MarketingProgramTypeID = ListMarketingProgramType.MarketingProgramTypeID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListTimeBase ON MarketingProgramMaster.TimeBaseID = ListTimeBase.TimeBaseID INNER JOIN " + "\r\n";
            queryString = queryString + "                       ListCustomerChannel ON MarketingProgramMaster.CustomerChannelID = ListCustomerChannel.CustomerChannelID INNER JOIN " + "\r\n";

            queryString = queryString + "                      (SELECT      MarketingProgramDetail.MarketingProgramID, MarketingProgramDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, MarketingProgramDetail.Remarks, " + "\r\n";
            queryString = queryString + "                                   MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, MarketingIncentiveDetail.Remarks AS IncentiveRemarks " + "\r\n";
            queryString = queryString + "                       FROM       (SELECT  MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks FROM MarketingProgramCustomerName GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail LEFT JOIN " + "\r\n";
            queryString = queryString + "                                   MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID " + "\r\n";
            queryString = queryString + "                       UNION ALL " + "\r\n";
            queryString = queryString + "                       SELECT      MarketingIncentiveDetail.MarketingProgramID, MarketingIncentiveDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, MarketingProgramDetail.Remarks, " + "\r\n";
            queryString = queryString + "                                   MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, MarketingIncentiveDetail.Remarks AS IncentiveRemarks " + "\r\n";
            queryString = queryString + "                       FROM       (SELECT  MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks FROM MarketingProgramCustomerName GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail RIGHT JOIN " + "\r\n";
            queryString = queryString + "                                   MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID" + "\r\n";
            queryString = queryString + "                       WHERE       MarketingProgramDetail.MarketingProgramID IS NULL" + "\r\n";
            queryString = queryString + "                       ) MarketingProgramCollection ON MarketingProgramMaster.MarketingProgramID = MarketingProgramCollection.MarketingProgramID INNER JOIN " + "\r\n";

            queryString = queryString + "                       ListCustomerName ON MarketingProgramCollection.CustomerID = ListCustomerName.CustomerID LEFT JOIN " + "\r\n";

            queryString = queryString + "                       ListMarketingPaymentType ON MarketingProgramCollection.MarketingPaymentTypeID = ListMarketingPaymentType.MarketingPaymentTypeID LEFT JOIN " + "\r\n";
            queryString = queryString + "                       ListMarketingPaymentTerm ON MarketingProgramCollection.MarketingPaymentTermID = ListMarketingPaymentTerm.MarketingPaymentTermID " + "\r\n";

            return queryString;
        }



        //////SELECT     MarketingProgramMaster.MarketingProgramID, MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode, 
        //////                      MarketingProgramMaster.MarketingProgramName, MarketingProgramMaster.MarketingProgramSubName, ListMarketingProgramGroup.Description AS ProgramGroup, 
        //////                      ListMarketingProgramType.Description AS ProgramType, ListCustomerChannel.Description AS CustomerTag, ListTimeBase.Description AS TimeBase, 
        //////                      MarketingProgramMaster.BeginingDate, MarketingProgramMaster.EndingDate, MarketingProgramMaster.SalesTarget AS TotalSalesTarget, 
        //////                      MarketingProgramMaster.MarketingBudget AS TotalMarketingBudget, MarketingProgramMaster.Remarks
        //////FROM         MarketingProgramMaster INNER JOIN
        //////                      ListMarketingProgramGroup ON MarketingProgramMaster.MarketingProgramGroupID = ListMarketingProgramGroup.MarketingProgramGroupID INNER JOIN
        //////                      ListMarketingProgramType ON MarketingProgramMaster.MarketingProgramTypeID = ListMarketingProgramType.MarketingProgramTypeID INNER JOIN
        //////                      ListTimeBase ON MarketingProgramMaster.TimeBaseID = ListTimeBase.TimeBaseID INNER JOIN
        //////                      ListCustomerChannel ON MarketingProgramMaster.CustomerChannelID = ListCustomerChannel.CustomerChannelID








        //////select * from (

        //////        SELECT     MarketingProgramDetail.MarketingProgramID, MarketingProgramDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, 
        //////                      MarketingProgramDetail.Remarks, MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, 
        //////                      MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, 
        //////                      MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, 
        //////                      MarketingIncentiveDetail.Remarks AS IncentiveRemarks
        //////FROM         (SELECT     MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks
        //////                       FROM          MarketingProgramCustomerName
        //////                       GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail LEFT JOIN
        //////                      MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND 
        //////                      MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID

        //////UNION ALL


        //////        SELECT     MarketingIncentiveDetail.MarketingProgramID, MarketingIncentiveDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, 
        //////                      MarketingProgramDetail.Remarks, MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, 
        //////                      MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, 
        //////                      MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, 
        //////                      MarketingIncentiveDetail.Remarks AS IncentiveRemarks
        //////FROM         (SELECT     MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks
        //////                       FROM          MarketingProgramCustomerName
        //////                       GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail RIGHT JOIN
        //////                      MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND 
        //////                      MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID
        //////                      WHERE MarketingProgramDetail.MarketingProgramID IS NULL) MarketingProgramCollection











        //        SELECT     MarketingProgramMaster.MarketingProgramID, MarketingProgramMaster.Reference, MarketingProgramMaster.ReferenceCode, 
        //                      MarketingProgramMaster.MarketingProgramName, MarketingProgramMaster.MarketingProgramSubName, ListMarketingProgramGroup.Description AS ProgramGroup, 
        //                      ListMarketingProgramType.Description AS ProgramType, ListCustomerChannel.Description AS CustomerTag, ListTimeBase.Description AS TimeBase, 
        //                      MarketingProgramMaster.BeginingDate, MarketingProgramMaster.EndingDate, MarketingProgramMaster.SalesTarget AS TotalSalesTarget, 
        //                      MarketingProgramMaster.MarketingBudget AS TotalMarketingBudget, MarketingProgramMaster.Remarks, MarketingProgramCustomerName.CustomerID, 
        //                      ListCustomerName.Description, ListCustomerName.DescriptionOfficial, MarketingProgramCustomerName.EffectedDate, 
        //                      MarketingProgramCustomerName.SalesTarget, MarketingProgramCustomerName.Remarks AS CustomerRemarks
        //FROM         ListCustomerName INNER JOIN
        //                      MarketingProgramCustomerName ON ListCustomerName.CustomerID = MarketingProgramCustomerName.CustomerID RIGHT OUTER JOIN
        //                      MarketingProgramMaster INNER JOIN
        //                      ListMarketingProgramGroup ON MarketingProgramMaster.MarketingProgramGroupID = ListMarketingProgramGroup.MarketingProgramGroupID INNER JOIN
        //                      ListMarketingProgramType ON MarketingProgramMaster.MarketingProgramTypeID = ListMarketingProgramType.MarketingProgramTypeID INNER JOIN
        //                      ListTimeBase ON MarketingProgramMaster.TimeBaseID = ListTimeBase.TimeBaseID INNER JOIN
        //                      ListCustomerChannel ON MarketingProgramMaster.CustomerChannelID = ListCustomerChannel.CustomerChannelID ON 
        //                      MarketingProgramCustomerName.MarketingProgramID = MarketingProgramMaster.MarketingProgramID



        //        SELECT     MarketingProgramDetail.MarketingProgramID, MarketingProgramDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, 
        //                      MarketingProgramDetail.Remarks, MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, 
        //                      MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, 
        //                      MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, 
        //                      MarketingIncentiveDetail.Remarks AS IncentiveRemarks
        //FROM         (SELECT     MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks
        //                       FROM          MarketingProgramCustomerName
        //                       GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail LEFT JOIN
        //                      MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND 
        //                      MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID



        //        SELECT     MarketingIncentiveDetail.MarketingProgramID, MarketingIncentiveDetail.CustomerID, MarketingProgramDetail.EffectedDate, MarketingProgramDetail.SalesTarget, 
        //                      MarketingProgramDetail.Remarks, MarketingIncentiveDetail.RequestedDate, MarketingIncentiveDetail.MarketingPaymentTypeID, 
        //                      MarketingIncentiveDetail.MarketingPaymentTermID, MarketingIncentiveDetail.PaymentPeriod, MarketingIncentiveDetail.TurnOverAmount, 
        //                      MarketingIncentiveDetail.IncentiveAmount, MarketingIncentiveDetail.PaidAmount, MarketingIncentiveDetail.Description, 
        //                      MarketingIncentiveDetail.Remarks AS IncentiveRemarks
        //FROM         (SELECT     MarketingProgramID, CustomerID, MIN(EffectedDate) AS EffectedDate, SUM(SalesTarget) AS SalesTarget, MAX(Remarks) AS Remarks
        //                       FROM          MarketingProgramCustomerName
        //                       GROUP BY MarketingProgramID, CustomerID) AS MarketingProgramDetail RIGHT JOIN
        //                      MarketingIncentiveDetail ON MarketingProgramDetail.MarketingProgramID = MarketingIncentiveDetail.MarketingProgramID AND 
        //                      MarketingProgramDetail.CustomerID = MarketingIncentiveDetail.CustomerID
        //                      WHERE MarketingProgramDetail.MarketingProgramID IS NULL







    }



}

